Understanding Binary Logs and Point-in-Time Recovery in MySQL

Course: MySQL Training

Topic: Understanding Binary Logs and Point-in-Time Recovery

Course Link: MySQL Training Course – JustAcademy

Demo Link: Register for a Course Demo

JustAcademy Website: Visit JustAcademy

1. Introduction to Binary Logs and Point-in-Time Recovery

Database administrators must protect databases against accidental deletion, incorrect updates, application errors, hardware failures, and other unexpected events. A regular database backup helps restore data, but a backup alone may not contain every change made after the backup was created.

MySQL provides binary logging to record database events that change data or database structures. These logs can be used for replication and recovery. When a full backup is restored, the binary logs generated after that backup can be replayed to bring the database forward to a selected point in time.

Point-in-Time Recovery (PITR) is a recovery technique that restores a database to a specific time or event boundary, such as immediately before an accidental DELETE or DROP operation.

For example, suppose a database backup was created at 9:00 AM. At 11:30 AM, an administrator accidentally deletes important customer records. If the appropriate binary logs are available, the administrator can restore the backup and replay logged changes only up to the point immediately before the destructive operation.

Official documentation: MySQL Point-in-Time Recovery.

2. Learning Objectives

After completing this topic, learners should be able to:

• Explain what MySQL binary logs are and why they are important.
• Understand how binary logging records database events.
• Differentiate binary logs, general query logs, and error logs.
• Check whether binary logging is enabled.
• List binary log files and identify the active log.
• Understand the role of the mysqlbinlog utility.
• Explain full backups, incremental recovery, and PITR.
• Recover a database up to a specified time or binary log position.
• Recognize common recovery problems and apply safe troubleshooting practices.

3. What Is a MySQL Binary Log?

A binary log is a collection of log files that records database events associated with changes made by a MySQL server. Depending on the logging format and operation, these events can represent SQL statements, row changes, database structure changes, transaction boundaries, and related metadata.

Binary logs are stored in a binary format rather than as ordinary text files. The MySQL mysqlbinlog utility can decode their contents into a readable representation and can produce output that can be replayed through a MySQL client.

3.1 Purpose of Binary Logs

• Point-in-Time Recovery: Replays changes made after a backup, stopping at a selected time or event position.
• Replication: Supplies recorded changes that replicas can apply to keep their data synchronized with a source server.
• Change investigation: Helps administrators inspect logged database events when investigating accidental changes or operational incidents.
• Incremental recovery: Reduces the need to rely exclusively on a full backup when recovering more recent database changes.

3.2 What Is Usually Not Recorded?

Statements that only read information, such as ordinary SELECT and SHOW statements, are not generally recorded as data-changing binary log events. Binary logging is not a complete record of every action performed by every user. For a broader record of queries, MySQL provides other logging facilities, such as the general query log, which serves a different purpose.

4. How Binary Logging Works

When binary logging is enabled, MySQL records relevant events as database operations occur. Events are written in binary log files, which may be rotated as they grow or when a log flush is requested. The server also maintains an index file identifying the binary log files.

1. A client connects to MySQL and runs an operation.
2. MySQL processes the statement or transaction.
3. Relevant changes are recorded as binary log events according to the server's logging configuration.
4. Binary log files accumulate events in an ordered sequence.
5. For recovery, an administrator restores a suitable full backup and replays the required events from the binary logs.

4.1 Example of Database Changes

CREATE DATABASE training_db;
USE training_db;
CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(100),
    course_name VARCHAR(100)
);
INSERT INTO students VALUES
(1, 'Aarav', 'MySQL'),
(2, 'Meera', 'Database Administration');
UPDATE students
SET course_name = 'Advanced MySQL'
WHERE student_id = 1;

When binary logging is enabled, the relevant database-changing operations can generate binary log events. The exact representation depends on the server configuration and the binary logging format.

4.2 Important Characteristics

• Binary logs contain events, not simply a plain-text transcript of every SQL command.
• They are used for replication and recovery.
• They can contain sensitive information about database changes, so access must be controlled.
• Binary logging consumes disk space and may introduce some performance overhead.
• Binary log retention must be planned so that required recovery logs are not deleted too early.

Reference: MySQL Binary Log Documentation.

5. Binary Log Files and Their Structure

MySQL commonly creates binary log files with names such as binlog.000001, binlog.000002, or names based on the server's configured log basename. The actual names depend on the server configuration.

5.1 Binary Log Index

The index file helps MySQL track its binary log files. Administrators should avoid manually deleting active log files or editing the index without understanding the consequences. Use supported MySQL statements and configured retention policies to manage logs.

5.2 Binary Log Positions

Binary log positions are byte offsets within a log file. They are useful when a recovery must stop at an exact event boundary rather than at a timestamp. A position is meaningful only when paired with the correct log file.

6. Binary Logging Configuration

Binary logging must be enabled for the server to record the changes needed for future PITR. MySQL 8.4 enables binary logging by default in its standard configuration, but installations, managed services, and custom configurations can differ. Always verify the actual server settings.

6.1 Check Whether Binary Logging Is Enabled

SHOW VARIABLES LIKE 'log_bin';

If the value is ON, binary logging is enabled. If it is OFF, the server is not recording binary logs and you cannot reconstruct missing historical events that were never logged.

6.2 Check the Binary Log Basename

SHOW VARIABLES LIKE 'log_bin_basename';
SHOW VARIABLES LIKE 'log_bin_index';

These variables can help identify the configured log-file location and index file, subject to the server version and environment.

6.3 Check the Binary Logging Format

SHOW VARIABLES LIKE 'binlog_format';

The main binary logging formats are:

Do not change logging settings on a production server without reviewing the replication, recovery, storage, and operational implications. The supported settings and defaults depend on the MySQL version and environment.

7. Viewing Binary Log Information

MySQL provides statements for inspecting the binary logs available to the server.

7.1 List Binary Log Files

SHOW BINARY LOGS;

This lists the binary log files that MySQL currently knows about and provides information such as their sizes.

7.2 Identify the Current Binary Log Status

SHOW BINARY LOG STATUS;

In MySQL 8.4, SHOW BINARY LOG STATUS can be used to inspect the current binary log status, including the current log file and position. Older MySQL versions may use a different statement, such as SHOW MASTER STATUS. Check the documentation for your installed version.

7.3 Rotate the Binary Log

FLUSH BINARY LOGS;

This requests a binary log rotation. It does not delete the existing log history or replace a backup. Use it only when appropriate for your operational needs.

8. The mysqlbinlog Utility

mysqlbinlog is a command-line utility for reading binary log files. It converts binary log events into a readable form and can select events by time or position. Its output can be reviewed, saved, or replayed through a MySQL client.

8.1 Display Binary Log Events

mysqlbinlog /path/to/binlog.000001

Replace the example path with the real path to a binary log file that you are authorized to read.

8.2 Save Decoded Output to a File

mysqlbinlog /path/to/binlog.000001 > decoded_binlog.sql

The generated file is a decoded representation of the binary log. Review it carefully before replaying it, particularly when the log contains destructive operations or changes to multiple databases.

8.3 Read a Range of Events by Time

mysqlbinlog \
  --start-datetime="2026-10-17 09:00:00" \
  --stop-datetime="2026-10-17 10:00:00" \
  /path/to/binlog.000001

The example selects events within a time range according to the utility's timestamp handling. Confirm the server and system time zones, and inspect the resulting event boundaries before applying any recovery operation.

8.4 Select Events by Position

mysqlbinlog \
  --start-position=120 \
  --stop-position=850 \
  /path/to/binlog.000001

The positions above are illustrative. Real positions must be taken from the actual log. When using position-based recovery, identify the correct log file and ensure that the stop position does not split a transaction in an unsafe way.

8.5 Official Utility Reference

Read the official documentation for available options and version-specific behavior: mysqlbinlog — Utility for Processing Binary Log Files.

9. What Is Point-in-Time Recovery (PITR)?

Point-in-Time Recovery is a process for restoring a database to a selected point after a backup was created. It usually combines a full backup with the sequence of binary log events generated after that backup.

A full backup provides the starting state. Binary logs provide the subsequent recorded changes. Replaying only the events up to the desired recovery boundary produces a database state corresponding to that point, subject to the backup's consistency and the availability and completeness of the required logs.

9.1 Why PITR Is Needed

• Recover from accidental deletion of data.
• Reverse the impact of an incorrect update by restoring to an earlier state.
• Recover from an application bug that changed many records.
• Restore data to a known point before an unwanted schema change.
• Reduce data loss between the time of the last full backup and an incident.

9.2 Example Scenario

Suppose the database has the following history:

If the required binary logs are available, the recovery process can restore the 09:00 AM backup and replay changes through the safe point before the 10:40 AM deletion. This can preserve the valid changes made after the backup while avoiding the destructive event.

10. Difference Between Full Backup, Incremental Recovery, and PITR

11. Prerequisites for Point-in-Time Recovery

Before attempting PITR, verify the following requirements:

• A usable and consistent full backup created before the desired recovery point.
• Every required binary log file generated after the backup and up to the recovery boundary.
• The correct order of binary log files and their associated positions.
• Access to the MySQL server and suitable administrative permissions.
• Sufficient disk space for the backup, decoded output if needed, and recovery operations.
• A safe recovery destination, preferably a separate server or isolated instance for initial testing.
• A clear target time or event position that defines the intended recovery boundary.
• A documented procedure for validating restored data before it replaces production data.

Important: If a required binary log has been purged, lost, corrupted, or never generated, the missing events cannot be recreated from that log. Recovery may then be limited to the latest available backup and the logs that remain.

12. Step-by-Step Point-in-Time Recovery Workflow

Step 1: Identify the Incident and Target Time

Determine when the unwanted operation occurred. Use application logs, database monitoring, audit records, deployment history, or the decoded binary log to identify the approximate time and, if possible, the exact event.

For example, if an unwanted DROP TABLE occurred at 10:40 AM, the target is usually the point immediately before that event—not simply any time earlier that day.

Step 2: Preserve the Existing Evidence

Do not immediately purge logs, overwrite the current database, or experiment on the only available backup. Preserve the existing backup and relevant binary logs. If possible, make copies to protected storage and record their names and checksums.

Step 3: Locate the Suitable Full Backup

Choose a consistent backup created before the incident. Check the backup timestamp, the databases and tables it contains, and any metadata that records the binary log file and position associated with the backup.

Step 4: Restore the Backup to a Recovery Environment

Restore the full backup to a separate MySQL instance when possible. This allows you to verify the recovery procedure without overwriting production data.

A typical SQL dump restore might use a command similar to the following, depending on how the backup was created:

mysql -u recovery_user -p < full_backup.sql

If the backup contains multiple databases, explicit database-creation statements, or a different dump structure, the restore command may need to be adjusted. Confirm that the dump is appropriate for the target instance.

Step 5: Identify the Binary Logs to Replay

Determine the first log position after the restored backup and the sequence of binary log files that cover the desired recovery period. If the backup tool records the binary log coordinates, use that metadata rather than guessing.

On a running MySQL server, you can inspect available log files with:

SHOW BINARY LOGS;
SHOW BINARY LOG STATUS;

These statements show current server information; they do not replace the backup metadata needed to identify the correct starting point for recovery.

Step 6: Inspect Events Around the Incident

Decode a narrow time range around the incident to locate the unwanted statement or event. For example:

mysqlbinlog \
  --start-datetime="2026-10-17 10:35:00" \
  --stop-datetime="2026-10-17 10:45:00" \
  /path/to/binlog.000123

The file name and timestamps are examples only. Use the actual log file and incident time. Inspect transaction boundaries and relevant events to decide the safe recovery boundary.

Step 7: Replay Only the Required Events

For a time-based recovery, MySQL's mysqlbinlog utility supports start and stop time options. The following illustrates the general pattern for replaying selected events:

mysqlbinlog \
  --start-datetime="2026-10-17 09:00:00" \
  --stop-datetime="2026-10-17 10:39:59" \
  /path/to/binlog.000123 \
  | mysql -u recovery_user -p

This is a teaching example, not a universal production-ready command. In an actual recovery, the starting point must match the backup's recorded log coordinates, and the required sequence may span multiple log files. Timestamp boundaries alone can be insufficient for an exact recovery, so inspect the decoded events and consider position-based recovery for precise event boundaries.

When applying multiple log files, process them in the correct order through a single MySQL client connection where appropriate. Do not replay events already included in the restored backup.

Step 8: Validate the Recovered Database

After replaying the selected events, check the database structure and data. Confirm that valid changes made before the target point are present and that the unwanted event was not applied.

SHOW DATABASES;
USE training_db;
SHOW TABLES;
SELECT COUNT(*) FROM students;
SELECT * FROM students ORDER BY student_id;

Step 9: Decide How to Return to Production

Compare the recovered database with the current production database and assess any changes that occurred after the recovery target. If the recovery environment is correct, plan a controlled cutover or selectively restore the required data. Keep the original production data and logs until the recovery is verified and approved.

13. Point-in-Time Recovery Using Event Positions

Time-based recovery is convenient when the incident time is known, but event positions can offer a more precise boundary. A position is an offset within a particular binary log file.

13.1 Locate the Relevant Event

Use mysqlbinlog to inspect the section of a log around the incident. The output includes event metadata such as timestamps and positions, which can help identify the event that should not be replayed.

mysqlbinlog --verbose /path/to/binlog.000123

13.2 Apply a Selected Position Range

mysqlbinlog \
  --start-position=456 \
  --stop-position=789 \
  /path/to/binlog.000123

These positions are placeholders, not values to use directly. Select the correct boundaries from the actual log. Avoid cutting through a transaction or applying an event range that conflicts with the restored backup.

13.3 Time-Based Recovery Versus Position-Based Recovery

Official guide: Point-in-Time Recovery Using Event Positions.

14. Practical Example: Recover Before an Accidental DELETE

Imagine that a company stores employee information in a database. A valid backup exists, and binary logs cover the period after the backup. At 2:15 PM, an incorrect query deletes all employees in a department.

14.1 Sample Table

CREATE DATABASE company_db;
USE company_db;
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    department VARCHAR(50)
);
INSERT INTO employees VALUES
(1, 'Anaya', 'IT'),
(2, 'Rohan', 'HR'),
(3, 'Ishaan', 'IT');

14.2 An Accidental Operation

DELETE FROM employees
WHERE department = 'IT';

If this statement was committed and binary logging recorded the change, the relevant binary log can help identify the operation and establish a recovery boundary. A safe procedure is to restore the backup to a separate instance, replay only the events up to immediately before the destructive operation, and validate the employee records.

Do not simply replay the full log through the current end if the unwanted DELETE is included. That would reproduce the destructive operation instead of avoiding it.

15. Binary Logs and Database Backup Strategy

Binary logging complements, but does not replace, a backup strategy. A robust recovery plan includes tested full backups, retention of the necessary binary logs, protected storage, and a documented recovery procedure.

15.1 Recommended Strategy

1. Create regular consistent full backups.
2. Verify that binary logging is enabled and configured appropriately.
3. Preserve binary logs from the point associated with each backup onward for the required recovery window.
4. Store backups and log copies away from the database server when possible.
5. Restrict access to backups and binary logs.
6. Test restoration and PITR regularly in a non-production environment.
7. Document recovery targets, responsibilities, and validation steps.

15.2 Recovery Point Objective (RPO)

The Recovery Point Objective is the amount of data loss an organization is prepared to tolerate, measured in time. A sound backup and binary log strategy can reduce potential data loss, but actual recovery depends on whether the required backups and logs are complete and available.

15.3 Recovery Time Objective (RTO)

The Recovery Time Objective is the target time for restoring service after an incident. Recovery speed depends on database size, backup format, server resources, binary log volume, and validation and cutover procedures.

16. Common Binary Log and PITR Errors

17. Security Best Practices

• Restrict binary log access to authorized database administrators and recovery processes.
• Protect backup and log archives because they can reveal sensitive business data.
• Use secure connections when reading logs remotely.
• Use least-privilege accounts instead of routinely using the root account.
• Keep recovery copies in protected storage, preferably separate from the database host.
• Monitor disk usage so that log growth does not unexpectedly fill the server's storage.
• Test restore procedures before using them during a production incident.
• Do not edit or replay decoded binary log output without understanding the effects of each event.
• Maintain an incident record that identifies the backup, log files, recovery boundary, validation results, and final cutover decision.

18. Best Practices for Production Environments

1. Know your recovery chain: Record which binary log files and positions follow each backup.
2. Monitor retention: Ensure that log expiry does not remove files still needed for the required recovery period.
3. Protect against a single point of failure: Keep protected backup and log copies outside the primary server.
4. Use an isolated recovery server: Test the recovery before modifying the live database.
5. Verify the target: Check that the recovered database represents the intended point in time.
6. Use transaction-aware boundaries: Avoid replaying only part of a transaction.
7. Document version differences: Check commands and behavior against the exact MySQL version in use.
8. Run recovery drills: Measure the time required to restore and replay the logs.

19. Hands-On Practice Exercise

Objective: Understand binary log inspection and the PITR workflow in a development environment. Do not run destructive recovery experiments against a production database.

Exercise 1: Check Binary Logging

SHOW VARIABLES LIKE 'log_bin';
SHOW VARIABLES LIKE 'binlog_format';
SHOW BINARY LOGS;
SHOW BINARY LOG STATUS;

Record whether binary logging is enabled, which files are available, and the current file and position.

Exercise 2: Create a Practice Database

CREATE DATABASE pitr_practice;
USE pitr_practice;
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    price DECIMAL(10,2)
);
INSERT INTO products VALUES
(1, 'Keyboard', 1200.00),
(2, 'Mouse', 650.00),
(3, 'Monitor', 8500.00);

Ensure that binary logging is enabled and that the test operations are recorded. The exact steps for creating a consistent backup and obtaining its starting log coordinates depend on the chosen backup method.

Exercise 3: Create a Backup and Make Changes

Create a suitable test backup. Afterward, insert or update a record and then perform a clearly identified test operation. Inspect the relevant log events with mysqlbinlog and locate the operation in the event sequence.

Exercise 4: Practice Recovery

Restore the backup into a separate practice instance and replay only the appropriate events up to a target before the unwanted operation. Validate the result with SELECT statements and compare it with the expected database state.

Exercise 5: Document the Result

• What was the backup timestamp?
• Which binary log files were needed?
• What event identified the unwanted operation?
• Which timestamp or position was chosen as the stop boundary?
• Were all expected records present after recovery?
• What could prevent the same recovery from succeeding in a real environment?

20. Interview Questions and Answers

Q1. What is a binary log in MySQL?

Answer: A binary log is a sequence of files containing events that record relevant database changes. It supports replication and point-in-time recovery.

Q2. Why are binary logs important for database recovery?

Answer: They allow administrators to replay changes made after a full backup, helping recover a database to a more recent time or to a point before an unwanted operation.

Q3. What is Point-in-Time Recovery?

Answer: PITR restores a database from a backup and reapplies the appropriate binary log events until a selected time or event boundary.

Q4. Does a binary log replace a full backup?

Answer: No. A full backup provides the starting database state. Binary logs generally supply the subsequent changes needed to advance that state.

Q5. What is mysqlbinlog?

Answer: It is a MySQL command-line utility used to read and decode binary log files and select event ranges by time or position.

Q6. How can you list binary logs?

Answer: Run SHOW BINARY LOGS; with suitable privileges.

Q7. How do you check whether binary logging is enabled?

Answer: Run SHOW VARIABLES LIKE 'log_bin'; and inspect the returned value.

Q8. What is the difference between time-based and position-based recovery?

Answer: Time-based recovery selects events using timestamps, while position-based recovery selects events using byte offsets within a specific binary log file.

Q9. What happens if a required binary log is missing?

Answer: The complete change history may no longer be available. Recovery may have to stop at the last point supported by the available backup and logs.

Q10. Why should PITR be tested on a separate server?

Answer: An isolated server protects production data while allowing the administrator to verify the recovery sequence, boundary, and resulting database state.

Q11. Can binary logs contain sensitive information?

Answer: Yes. They can contain information about database changes, so they should be protected with appropriate access controls and secure storage.

Q12. What is the most important step before replaying binary logs?

Answer: Confirm the backup's starting coordinates, the correct log sequence, and the target recovery boundary. Inspect the relevant events before applying them.

21. Quick Revision Checklist

• Binary logs record relevant database-change events.
• Binary logging supports replication and point-in-time recovery.
• A full backup provides the starting state for typical PITR.
• The required binary logs must remain available and in the correct order.
• SHOW BINARY LOGS; lists available binary log files.
• SHOW BINARY LOG STATUS; reports the current binary log status in MySQL 8.4.
• mysqlbinlog decodes logs and supports time- and position-based selection.
• Recovery should stop before the unwanted event when that is the desired outcome.
• Backup coordinates and transaction boundaries matter.
• Always validate the recovered database before returning it to production.
• Protect backup files and binary logs as sensitive operational assets.

22. Conclusion

MySQL binary logs are an important component of database recovery and replication. They preserve a sequence of relevant database changes that can be inspected and replayed after a suitable backup has been restored. Point-in-Time Recovery uses that history to return a database to a selected time or event boundary, such as the state immediately before an accidental deletion.

Reliable PITR depends on more than knowing a command. Administrators must maintain consistent backups, preserve the necessary binary logs, identify the correct starting coordinates, select a safe stopping boundary, and validate the restored data. Regular practice and documented recovery procedures help reduce data loss and improve recovery readiness.

23. Official Learning Resources

• MySQL 8.4: The Binary Log
• MySQL 8.4: Point-in-Time Recovery
• Point-in-Time Recovery Using Binary Logs
• Point-in-Time Recovery Using Event Positions
• mysqlbinlog Utility Reference
• Explore MySQL Training at JustAcademy
• Register for a MySQL Training Demo